IF EXISTS (SELECT *
           FROM INFORMATION_SCHEMA.ROUTINES
           WHERE ROUTINE_NAME='pResetSell'
           AND ROUTINE_SCHEMA='dbo'
           AND ROUTINE_TYPE='PROCEDURE'
          )
          DROP PROCEDURE dbo.pResetSell
GO

CREATE PROC dbo.pResetSell
	@vbitSuccess INT OUTPUT
AS
/*@@*_____________________________________________________________________

  

_____________________________________________________________________*@@*/

/*______________________ CONSTANT DECLARATIONS _________________________*/

  DECLARE
    @cstrThisProc              NVARCHAR(128),
    @cintErrGeneral            INT

  SET @cstrThisProc            = N'pResetSell'
  SET @cintErrGeneral          = 50000  -- General error. Used when an
                                        -- error has occurred and no other
                                        -- specific error condition is
                                        -- assigned.

/*______________________ VARIABLE DECLARATIONS _________________________*/

  DECLARE
    @intErrNo       INT,
    @strErrMsg      NVARCHAR(400),
    @intReturn      INT

/*_______________________________ INIT _________________________________*/

  -- Setting NOCOUNT ON prevents additional recordsets with the
  -- "number of records affected" from being returned, which is not
  -- handled the same by all providers, such as for OLEDB vs. ODBC.
  SET NOCOUNT ON

/*________________________ VALIDATE PARAMETERS _________________________*/


/*_______________________________ MAIN _________________________________*/
	SET @vbitSuccess = 0
	
	BEGIN TRANSACTION
	BEGIN TRY
	
	-- Delete ReturnProductDetail
	DELETE FROM ReturnProductDetail
	
	-- Delete ReturnProduct
	DELETE FROM ReturnProduct
	
	-- Delete BillDetail
	DELETE FROM BillDetail
	
	-- Delete Bill
	DELETE FROM Bill

	-- Return value 1 indicates success
	SET @vbitSuccess = 1
	END TRY
	BEGIN CATCH
		GOTO lblError
	END CATCH
	
	IF @@TRANCOUNT > 0 BEGIN
		COMMIT TRANSACTION
	END
	RETURN

/*___________________________ ERROR HANDLER ____________________________*/
lblError:
  -- An error has occurred.  On entering this section @intErrNo should
  -- already hold the error number
	
	IF @@TRANCOUNT > 0 BEGIN
		ROLLBACK TRANSACTION
	END
	
	SET @vbitSuccess = 0
	
	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;
	
	SELECT 
		@ErrorMessage = ERROR_MESSAGE(),
		@ErrorSeverity = ERROR_SEVERITY(),
		@ErrorState = ERROR_STATE();

	-- Use RAISERROR inside the CATCH block to return error
	-- information about the original error that caused
	-- execution to jump to the CATCH block.
	RAISERROR (@ErrorMessage, -- Message text.
			   @ErrorSeverity, -- Severity.
			   1 -- State.
			   );


